package com.example.teadoctor;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TeaDBHelper extends SQLiteOpenHelper {

    SQLiteDatabase db;
    // 茶表格的字段名称
    private static final String TABLE_TEA_INFO = "tea_info";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_NAME = "name";
    private static final String COLUMN_IMAGE = "image";
    private static final String COLUMN_TYPE = "type";
    private static final String COLUMN_YEAR = "year";
    private static final String COLUMN_DESCRIPTION = "description";
    private static final String COLUMN_METHOD = "method";
    private static final String COLUMN_STORAGE = "storage";
    private static final String COLUMN_DATE = "date";

    //茶信息的创建语句
    private static final String CREATE_TABLE_TEA_INFO = "CREATE TABLE " +
            TABLE_TEA_INFO + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            COLUMN_NAME + " TEXT NOT NULL," +
            COLUMN_IMAGE + " TEXT NOT NULL," +
            COLUMN_TYPE + " TEXT NOT NULL," +
            COLUMN_YEAR + " INTEGER NOT NULL," +
            COLUMN_DESCRIPTION + " TEXT NOT NULL," +
            COLUMN_METHOD + " TEXT NOT NULL," +
            COLUMN_STORAGE + " TEXT NOT NULL," +
            COLUMN_DATE + " TEXT NOT NULL" + ")";
    //插入数据
    private static final String INSERT_TABLE_TEA_INFO = "INSERT INTO tea_info (name, image, type, year, description, method, storage, date)\n" +
            "VALUES\n" +
            "    ('铁观音', 'http://43.138.141.94:8083/image/tea1.jpg', '烘青', '2020', 'http://43.138.141.94:8083/tea_description/tea_description1.txt', '热水冲泡', '通风干燥', '2021-04-01'),\n" +
            "    ('普洱茶', 'http://43.138.141.94:8083/image/tea2.jpg', '熟茶', '2018', 'http://43.138.141.94:8083/tea_description/tea_description2.txt', '沸水冲泡', '避光存储', '2021-04-02'),\n" +
            "    ('龙井茶', 'http://43.138.141.94:8083/image/tea3.jpg', '炒青', '2021', 'http://43.138.141.94:8083/tea_description/tea_description3.txt', '85℃水冲泡', '阴凉存放', '2021-04-03'),\n" +
            "    ('赤霞珠红茶', 'http://43.138.141.94:8083/image/tea4.jpg', '红茶', '2019', 'http://43.138.141.94:8083/tea_description/tea_description4.txt', '沸水冲泡', '常温存储', '2021-04-04'),\n" +
            "    ('碧螺春', 'http://43.138.141.94:8083/image/tea5.jpg', '炒青', '2021', 'http://43.138.141.94:8083/tea_description/tea_description5.txt', '80℃水冲泡', '通风干燥', '2021-04-05'),\n" +
            "    ('安溪铁观音', 'http://43.138.141.94:8083/image/tea6.jpg', '烘青', '2020', 'http://43.138.141.94:8083/tea_description/tea_description6.txt', '热水冲泡', '通风干燥', '2021-04-06'),\n" +
            "    ('祁门红茶', 'http://43.138.141.94:8083/image/tea7.jpg', '红茶', '2018', 'http://43.138.141.94:8083/tea_description/tea_description7.txt', '沸水冲泡', '常温存储', '2021-04-07'),\n" +
            "    ('武夷岩茶', 'http://43.138.141.94:8083/image/tea8.jpg', '烘青', '2021', 'http://43.138.141.94:8083/tea_description/tea_description8.txt', '100℃水冲泡', '避光存储', '2021-04-08'),\n" +
            "    ('六安瓜片', 'http://43.138.141.94:8083/image/tea9.jpg', '炒青', '2020', 'http://43.138.141.94:8083/tea_description/tea_description9.txt', '热水冲泡', '凉爽干燥', '2021-04-09'),\n" +
            "    ('洞庭碧螺春', 'http://43.138.141.94:8083/image/tea10.jpg', '炒青', '2021', 'http://43.138.141.94:8083/tea_description/tea_description10.txt', '90℃水冲泡', '通风干燥', '2021-04-10'),\n" +
            "    ('黄山毛峰', 'http://43.138.141.94:8083/image/tea11.jpg', '炒青', '2020', 'http://43.138.141.94:8083/tea_description/tea_description11.txt', '85℃水冲泡', '阴凉存放', '2021-04-09'),\n" +
            "    ('君山银针', 'http://43.138.141.94:8083/image/tea12.jpg', '白茶', '2021', 'http://43.138.141.94:8083/tea_description/tea_description12.txt', '80℃水冲泡', '避光存储', '2021-04-12'),\n" +
            "    ('太平猴魁', 'http://43.138.141.94:8083/image/tea13.jpg', '炒青', '2020', 'http://43.138.141.94:8083/tea_description/tea_description13.txt', '90℃水冲泡', '通风干燥', '2021-04-13'),\n" +
            "    ('祁红', 'http://43.138.141.94:8083/image/tea14.jpg', '红茶', '2019', 'http://43.138.141.94:8083/tea_description/tea_description14.txt', '沸水冲泡', '阴凉存放', '2021-04-14'),\n" +
            "    ('信阳毛尖', 'http://43.138.141.94:8083/image/tea15.jpg', '炒青', '2021', 'http://43.138.141.94:8083/tea_description/tea_description15.txt', '85℃水冲泡', '常温存储', '2021-04-15'),\n" +
            "    ('白牡丹', 'http://43.138.141.94:8083/image/tea16.jpg', '白茶', '2020', 'http://43.138.141.94:8083/tea_description/tea_description16.txt', '80℃水冲泡', '避光存储', '2021-04-16'),\n" +
            "    ('黄山绿茶', 'http://43.138.141.94:8083/image/tea17.jpg', '炒青', '2021', 'http://43.138.141.94:8083/tea_description/tea_description17.txt', '80℃水冲泡', '通风干燥', '2021-04-17'),\n" +
            "    ('仙女乌龙', 'http://43.138.141.94:8083/image/tea18.jpg', '烘青', '2020', 'http://43.138.141.94:8083/tea_description/tea_description18.txt', '热水冲泡', '阴凉存放', '2021-04-18'),\n" +
            "    ('四季春', 'http://43.138.141.94:8083/image/tea19.jpg', '炒青', '2021', 'http://43.138.141.94:8083/tea_description/tea_description19.txt', '85℃水冲泡', '常温存储', '2021-04-19'),\n" +
            "    ('红茶玛奇朵', 'http://43.138.141.94:8083/image/tea20.jpg', '红茶', '2021', 'http://43.138.141.94:8083/tea_description/tea_description20.txt', '沸水冲泡', '常温存储', '2021-04-20');";


    // 创建头条信息表
    String sqlCreateHeadline = "CREATE TABLE headline " +
            "(headline_id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "headline_title TEXT NOT NULL," +
            "headline_time TEXT NOT NULL," +
            "headline_source TEXT NOT NULL," +
            "headline_text TEXT NOT NULL," +
            "headline_author TEXT NOT NULL," +
            "headline_imageurl TEXT NOT NULL)";

    //插入头条数据
    String sqlInsertHeadline = "INSERT INTO headline ( headline_title, headline_time , headline_source, headline_text, headline_author, headline_imageurl) " +
            "VALUES ( '河南桐柏：茶香飘八方 农民喜洋洋','2023-05-18','新华网', 'http://43.138.141.94:8083/headline/1.txt',' 王海霞 范作言','http://43.138.141.94:8083/headline/1.jpg'), " +
            "( '山东五莲：发展茶产业助推乡村振兴','2023-5-17', '新华网', 'http://43.138.141.94:8083/headline/2.txt','吕放','http://43.138.141.94:8083/headline/2.jpg'), " +
            "( '“茶和天下·雅集”之“重庆之窗”摄影展亮相巴黎','2023-5-17', '新华网', 'http://43.138.141.94:8083/headline/3.txt','刘笑冬','http://43.138.141.94:8083/headline/3.jpg'), " +
            "( '梧州市将开展“5·21国际茶日 邀您共饮梧州六堡茶”系列活动','2023-5-16', '梧州日报', 'http://43.138.141.94:8083/headline/4.txt','陈益铭 通讯员 李京乘','http://43.138.141.94:8083/headline/4.jpg'), " +
            "( '新茶亭 新形象 助推神鹊茶转型升级','2023-5-16', '观山湖区融媒体中心', 'http://43.138.141.94:8083/headline/5.txt','邓娴 郝涌智','http://43.138.141.94:8083/headline/5.jpg'), " +
            "( '2023日照茶缘跑开跑','2023-5-16', '岚山区委宣传部', 'http://43.138.141.94:8083/headline/6.txt','贾淼','http://43.138.141.94:8083/headline/6.jpg'), " +
            "( '贵州持续深耕茶品牌体系建设','2023-5-16', '贵州日报', 'http://43.138.141.94:8083/headline/7.txt','邓娴 刘昌馀','http://43.138.141.94:8083/headline/7.jpg'), " +
            "( '2023“一带一路”国际茶叶贸易论坛暨第五届湖北鹤峰茶商大会开幕','2023-5-15', '新华网', 'http://43.138.141.94:8083/headline/8.txt','陈剑','http://43.138.141.94:8083/headline/8.jpg'), " +
            "( '黔西南州“普安红”茶文化创业创新暨技能大赛举行','2023-5-15', '黔西南日报', 'http://43.138.141.94:8083/headline/9.txt','吴雨','http://43.138.141.94:8083/headline/9.jpg'), " +
            "( '厦门茶博会吸引外国茶商觅商机','2023-5-14', '新华社', 'http://43.138.141.94:8083/headline/10.txt','周楚卿','http://43.138.141.94:8083/headline/10.jpg')";

    // 创建茶经营信息表
    String sqlCreateTeaBusiness = "CREATE TABLE tea_business " +
            "(business_id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "business_title TEXT NOT NULL," +
            "business_image TEXT NOT NULL," +
            "business_information TEXT NOT NULL," +
            "business_date TEXT NOT NULL)";


    // 插入茶经营信息数据
    String sqlInsertTeaBusiness = "INSERT INTO tea_business ( business_title, business_image , business_information, business_date) " +
            "VALUES ( '如何营销单一茶品','http://43.138.141.94:8083/image/b1.jpg','http://43.138.141.94:8083/business/business1.txt', '2021-06-01'), " +
            "( '如何组织茶文化活动','http://43.138.141.94:8083/image/b2.jpg', 'http://43.138.141.94:8083/business/business2.txt', '2021-06-05'), " +
            "( '如何推广新品种茶叶','http://43.138.141.94:8083/image/b3.jpg', 'http://43.138.141.94:8083/business/business3.txt', '2021-07-02'), " +
            "( '如何参加茶叶展销会','http://43.138.141.94:8083/image/b4.jpg', 'http://43.138.141.94:8083/business/business4.txt', '2021-07-10'), " +
            "( '如何运营茶叶电商','http://43.138.141.94:8083/image/b5.jpg', 'http://43.138.141.94:8083/business/business5.txt', '2021-08-08'), " +
            "( '如何打造茶叶的品牌效应','http://43.138.141.94:8083/image/b6.jpg','http://43.138.141.94:8083/business/business6.txt',  '2021-08-12'), " +
            "( '如何开拓区域茶叶市场','http://43.138.141.94:8083/image/b7.jpg', 'http://43.138.141.94:8083/business/business7.txt', '2021-09-01'), " +
            "( '如何营销特色工艺茶','http://43.138.141.94:8083/image/b8.jpg', 'http://43.138.141.94:8083/business/business8.txt', '2021-09-05'), " +
            "( '如何扩大茶叶销售渠道','http://43.138.141.94:8083/image/b9.jpg', 'http://43.138.141.94:8083/business/business9.txt', '2021-10-03'), " +
            "( '如何积累茶叶品牌口碑','http://43.138.141.94:8083/image/b10.jpg','http://43.138.141.94:8083/business/business10.txt', '2021-10-10')";

    public TeaDBHelper(Context context) {
        super(context,"tea.db",null,1);
    }

    //创建茶买卖信息表
    String sqlCreateTeaTrade = "CREATE TABLE tea_trade " +
            "(business_id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "tea_id INTEGER NOT NULL REFERENCES tea_info(tea_id)," +
            "tea_price REAL NOT NULL," +
            "supply TEXT NOT NULL," +
            "trade_amount INTEGER NOT NULL," +
            "trade_date TEXT NOT NULL)";

    //插入茶买卖信息数据
    String sqlInsertTeaTrade = "INSERT INTO tea_trade (tea_id, tea_price, supply, trade_amount, trade_date) " +
            "VALUES (1, 50.0, '供应商A', 100, '2022-06-01'), " +
            "(1, 60.0, '客户B', 50, '2022-06-05'), " +
            "(2, 30.0, '供应商C', 200, '2022-07-02'), " +
            "(13, 80.0, '客户D', 70, '2023-01-10'), " +
            "(4, 45.0, '供应商A', 150, '2021-08-08'), " +
            "(12,  65.0, '客户E', 80, '2021-08-12'), " +
            "(3, 35.0, '供应商B', 100, '2021-09-01'), " +
            "(5, 90.0, '客户F', 40, '2021-09-05'), " +
            "(2, 25.0, '供应商D', 300, '2021-10-03'), " +
            "(14, 70.0, '客户G', 90, '2021-10-10')";



    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建茶信息表格、经营表，并执行插入语句
        db.execSQL(CREATE_TABLE_TEA_INFO);
        db.execSQL(INSERT_TABLE_TEA_INFO);
        db.execSQL(sqlCreateTeaBusiness);
        db.execSQL(sqlInsertTeaBusiness);
        db.execSQL(sqlCreateTeaTrade);
        db.execSQL(sqlInsertTeaTrade);
        db.execSQL(sqlCreateHeadline);
        db.execSQL(sqlInsertHeadline);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS tea_info");
        db.execSQL("DROP TABLE IF EXISTS tea_business");
        db.execSQL("DROP TABLE IF EXISTS tea_trade");
        db.execSQL("DROP TABLE IF EXISTS user");
        onCreate(db);
    }

}
